const mysql = require('mysql2/promise'),
      { con, ResData } = require('./common');
      
/*查询所有动态*/
async function getCommunity(){
  let res = new ResData();
  let sql = `select comm_id, comm_text, comm_time, comm_img, community.user_id, user_name, user_avatar 
    from users, community
    where users.user_id = community.user_id
    order by comm_id desc;`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
};
/*查询所有动态*/
async function getUserCommunity(user_id){
  let res = new ResData();
  let sql = `select comm_id, comm_text, comm_time, comm_img, community.user_id, user_name, user_avatar 
    from users, community
    where users.user_id = community.user_id and users.user_id = ${user_id}
    order by comm_id desc;`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
};
/*分页查询所有动态*/
async function getCommByPage(page, limit){
  let res = new ResData();
  let sql = `select comm_id, comm_text, comm_time, comm_img, community.user_id, user_name, user_avatar 
    from users, community
    where users.user_id = community.user_id
    order by comm_id desc
    limit ${limit*(page-1)},${limit};`;
  console.log(sql);
  var [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
};

/*根据动态id查询图片*/
async function getCommimgByCommid(comm_id){
  let res = new ResData();
  let sql = `select community.comm_id, commimg_id, commimg_src
    from community, commimg
    where community.comm_id = commimg.comm_id and community.comm_id = ${comm_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}

/*根据动态id查询所有评论*/
async function getReviewsByCommid(comm_id){
  let res = new ResData();
  let sql = `select review_id, comm_id, reviews.user_id, user_name, user_avatar, review_text, review_time
    from users, reviews
    where  reviews.user_id = users.user_id and comm_id = ${comm_id};`;
  let [rows] = await con.execute(sql);

  res.data = rows;
  res.count = rows.length;
  return res;
}

/*添加评论*/
async function addReview(user_id, comm_id, review_text){
  let res = new ResData();
  let sql = `insert into reviews(user_id, comm_id, review_text, review_time)
    values('${user_id}', '${comm_id}', '${review_text}', '2020-12-01 12:00:00');`;
  try{
    let [rows] = await con.execute(sql);

  }catch(e){
    res.code = 401003;
    res.msg = 'insert failed';
  }

  return res;
}
/*获取comm_id*/
async function getCommid(){
  let res = new ResData();
  let sql1 = `delete from community
    where user_id is null and user_id is null and comm_time is null and comm_img is null;`;
  let sql2 = `insert into community() values ();`;
  let sql3 = `select comm_id
    from community
    where user_id is null and user_id is null and comm_time is null and comm_img is null;`
  try{
    await con.execute(sql1);
    await con.execute(sql2);
    let [rows] = await con.execute(sql3);
    let comm_id = rows[0].comm_id;
    res.data = comm_id;
  }catch(e){
    res.code = 401003;
    res.msg = 'getcommid failed';
  }
  return res;
}
/*发表动态*/
async function addComm(comm_id,comm_text,user_id, img, comm_time){
  console.log('comm_id: '+comm_id);
  console.log('comm_text: '+comm_text);
  console.log('user_id: '+user_id);
  console.log('img: '+img);
  let sql0 = `select comm_img from community where comm_id = ${comm_id};`;
  let [rows] = await con.execute(sql0);
  let old_img = (rows[0].comm_img == null?'':rows[0].comm_img+',');
  console.log("old_img: "+old_img);
  let comm_img = old_img + img + ',';
  comm_img = comm_img.slice(0,comm_img.length-1);
  console.log('comm_img: '+ comm_img);
  let res = new ResData();
  let sql = `update community 
  set comm_text = '${comm_text}' , user_id = ${user_id} , comm_time = '${comm_time}', comm_img = '${comm_img}'
  where comm_id = ${comm_id};`;
  console.log(sql);
  try{
    [rows] = await con.execute(sql);
    res.data = 'ok';
  }catch(e){
    res.code = 401003;
    res.msg = 'insert failed';
  }
  return res;
}
/*发表动态--图片*/
async function addCommimg(comm_id, commimg_src){
  let res = new ResData();
  let sql = `insert into commimg(comm_id, commimg_src)
    values ('${comm_id}', '${commimg_src}');`;
  console.log(sql);
  try{
    let [rows] = await con.execute(sql);
    res.msg = rows.affectedrows === 1 ? 'ok' : 'insert failed';
  }catch(e){
    res.code = 401003;
    res.msg = 'insert failed';
  }
  return res;
}

/*根据动态id删除动态*/
async function delCommByCommid(comm_id){
  let res = new ResData();
  let sql = `delete from community
    where comm_id = ${comm_id};`;
  let [rows] = await con.execute(sql);
  return res;
}
/*根据评论id删除评论*/
async function delReviewByReviewid(review_id){
  let res = new ResData();
  let sql = `delete from reviews
    where review_id = ${review_id};`;
  let [rows] = await con.execute(sql);
  return res;
}

/*获取点赞总数*/
async function getLikes(comm_id){
  let res = new ResData();
  let sql = `select comm_id, user_id from likes where comm_id = ${comm_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  return res;
}
/*点赞*/
async function addLike(comm_id, user_id){
  let res = new ResData();
  let sql = `insert into likes(comm_id, user_id) values(${comm_id}, ${user_id});`;
  console.log(sql);
  try{
    let [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401003;
    res.msg = 'insert failed';
  }
  return res;
}
/*取消点赞*/
async function delLike(comm_id, user_id){
  let res = new ResData();
  let sql = `delete from likes where comm_id=${comm_id} and user_id=${user_id};`;
  console.log(sql);
  try{
    let [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401003;
    res.msg = 'insert failed';
  }
  return res;
}
/*获取点赞列表*/
async function getLikelist(){
  let res = new ResData();
  let sql = `select community.comm_id, count(likes.user_id) as likes_count
		from community left join likes on community.comm_id = likes.comm_id
		group by community.comm_id
        order by community.comm_id desc;`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  return res;
}


module.exports = {
  getCommunity,
  getUserCommunity,
  getCommByPage,
  getCommimgByCommid,
  addReview,
  getReviewsByCommid,
  addComm,
  addCommimg,
  getCommid,
  delCommByCommid,
  delReviewByReviewid,
  getLikes,
  addLike,
  delLike,
  getLikelist

};
